package com.wiloon.java8x.jdbc;

import java.sql.*;

public class JDBC_Test {
    static Connection conn;

    static Statement st;

    public static void main(String[] args) {
        insert();
        update();
        delete();
        query();
    }


    public static void insert() {

        conn = getConnection();

        try {
            String sql = "INSERT INTO tabuser(id, name)"
                    + " VALUES (0, 'testuser')";

            st = (Statement) conn.createStatement();

            int count = st.executeUpdate(sql);
            conn.close();

        } catch (SQLException e) {
            System.out.println(" " + e.getMessage());
        }
    }


    public static void update() {
        conn = getConnection();
        try {
            String sql = "update staff set wage='2200' where name = 'lucy'";

            st = (Statement) conn.createStatement();

            int count = st.executeUpdate(sql);

            System.out.println(" " + count + " ");

            conn.close();

        } catch (SQLException e) {
            System.out.println(" ");
        }
    }


    public static void query() {

        conn = getConnection();
        try {
            String sql = "select * from staff";
            st = (Statement) conn.createStatement();

            ResultSet rs = st.executeQuery(sql);
            System.out.println(" ");
            while (rs.next()) {


                String name = rs.getString("name");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");
                String address = rs.getString("address");
                String depart = rs.getString("depart");
                String worklen = rs.getString("worklen");
                String wage = rs.getString("wage");


                System.out.println(name + " " + age + " " + sex + " " + address
                        + " " + depart + " " + worklen + " " + wage);

            }
            conn.close();

        } catch (SQLException e) {
            System.out.println(" ");
        }
    }


    public static void delete() {

        conn = getConnection();
        try {
            String sql = "delete from staff  where name = 'lili'";
            st = (Statement) conn.createStatement();

            int count = st.executeUpdate(sql);

            System.out.println("  " + count + "  \n");

            conn.close();

        } catch (SQLException e) {
            System.out.println(" ");
        }

    }


    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/beb", "bebmain", "bebm@in");

        } catch (Exception e) {
            System.out.println(" " + e.getMessage());
        }
        return con;
    }
}